package com.xsy.middleware.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xsy.middleware.service.DataBackService;
import com.xsy.middleware.utils.CrmApiUtils;
import com.xsy.middleware.utils.ResultVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Description: 初始化crm基本配置表
 * @Author: 黎炎松
 * @Date: 2024/4/25 11:15
 */
@Slf4j
@Controller
public class InitCrmBaseTable {

    @Autowired
    private CrmApiUtils crmApiUtils;

    @Autowired
    private DataBackService backService;

    public static void main(String[] args) {
        InitCrmBaseTable initCrmBaseTable = new InitCrmBaseTable();
        List<String> initTableList2 = initCrmBaseTable.getInitTableList2();
        String collect = initTableList2.stream().map(v -> v).collect(Collectors.joining(","));
        System.out.println("collect = " + collect);
    }

    /**
     *  根据 excel 表格 获取到crm 需要初始化的表
     * @return
     */
    public static List<String> getInitTableList2() {
        List<String> initTableList = new ArrayList<>();
        try (
                InputStream inputStream = InitCrmBaseTable.class.getClassLoader().getResourceAsStream("订单id.xlsx")
        ) {
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            rowIterator.next(); // 去除第一个行表头
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Cell cell = row.getCell(0);
                initTableList.add(cell.getStringCellValue());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  initTableList;
    }


    /**
     * 初始化 - 根据 excel 批量生成 crm 配置表
     * @return
     */
    public ResultVo batchInitCrmBaseTable() {
        log.info("开始初始化Crm表格 ======>");
        List<String> initTableList = getInitTableList();
        for (int i = 0; i < initTableList.size(); i++) {
            String tableName = initTableList.get(i);
            log.info("初始化Crm表格 ======> {} - {}", i, tableName);
            createCrmBaseTable(tableName);
        }
        log.info("完成初始化Crm表格 ======>");
        return ResultVo.success("完成初始化Crm表格");
    }

    /**
     * 删除CRM配置表
     * @param tableName
     * @return
     */
    public ResultVo delCrmBaseTable(String tableName) {
        try {
            // 查询CRM是否有当前配置表
            JSONObject dataBackup = backService.getDataBackup(tableName);
            if (dataBackup == null) {
                return ResultVo.success();
            }

            // 有配置表，需要进行删除
            log.info("删除CRM配置表 ======> {}", tableName);
            Long id = dataBackup.getLong("id");
            ResultVo resultVo = crmApiUtils.delete("dataBackup__c", id);
            if (resultVo.getCode() != 200) {
                log.info(resultVo.getMsg());
            }
            return resultVo;
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 创建 crm 配置表
     * @param tableName
     * @return
     */
    public ResultVo createCrmBaseTable(String tableName) {
        try {
            log.info("crm添加配置表 ======> {}", tableName);
            JSONObject tableData = crmApiUtils.queryEntityDesc(tableName);

            // 如果是查询订单需要特殊处理 查询的表名需要调整为 _order
            if (tableName.equals("order")) {
                tableName = "_order";
            }

            // 删除CRM配置表数据（新增前先进行判断是否已经存在，存在则删除）
            delCrmBaseTable(tableName);

            // 添加配置表
            JSONObject dataBackup = new JSONObject();
            dataBackup.put("entityType", 3253145227149979L);
            dataBackup.put("objectName__c", tableData.get("label"));
            dataBackup.put("objectApiKey__c", tableName);
            dataBackup.put("ownerId", 1635489299645095L);
            ResultVo addResultVo = crmApiUtils.insert("dataBackup__c", dataBackup);
            log.info("crm添加配置表 ======> {}", addResultVo);
            if (addResultVo.getCode() != 200) {
                return ResultVo.error("添加配置表失败: " + addResultVo.getMsg());
            }

            // 获取配置添加配置表明细
            Long dataBackupId = (Long) addResultVo.getData();
            JSONArray fieldList = tableData.getJSONArray("fields");
            createDataBackupDetail(tableName, dataBackupId, fieldList);

            log.info("crm新增配置表成功 ======> {}", tableName);
            return ResultVo.success("crm新增配置表成功: " + tableName);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 创建crm配置表明细
     * @param dataBackupId
     * @param fieldList
     */
    private void createDataBackupDetail(String tableName, Long dataBackupId, JSONArray fieldList) throws UnsupportedEncodingException {
        // 用户表同步查询报错的字段: passwordRuleId isVirtual relatedDepart relatedProduct relatedArea relatedBusiness relatedIndustry
        List<String> userErrorField = Arrays.asList("passwordRuleId", "isVirtual", "relatedDepart", "relatedProduct", "relatedArea", "relatedBusiness", "relatedIndustry");
        log.info("crm添加配置表详情 ======> ");
        for (Object field : fieldList) {
            JSONObject fieldData = (JSONObject) field;
            JSONObject backupDetail = new JSONObject();
            backupDetail.put("dataBackup__c", dataBackupId);
            backupDetail.put("fieldName__c", fieldData.get("label"));
            backupDetail.put("fieldApiKey__c", fieldData.get("apiKey"));
            backupDetail.put("fieldType__c", fieldData.get("itemType"));

            // 判断是否是文件类型
            String crmType = fieldData.getString("itemType");
            if ("List<Map>".equals(crmType)) {
                backupDetail.put("isFile__c", 1);
            }

            // 设置数据库字段类型
            backupDetail.put("dbFieldType__c", crmTypeToDbType(fieldData));
            backupDetail.put("entityType", 3253158667469600L);

            // 如果是用户表 需要把以下字段设置为不同步（查询数据会导致报错）
            if (tableName.equals("user") && userErrorField.contains(fieldData.get("apiKey"))) {
                backupDetail.put("isSync__c", 2);
            }

            // 保存 (自定义表不支持批量新增)
            ResultVo addResultVo = crmApiUtils.insert("dataBackupDetail__c", backupDetail);
            log.info("crm添加配置表详情 ======> {}", addResultVo);
        }

    }

    /**
     * 将CRM模块的字段类型转换为数据库的字段类型
     * @param fieldData
     * @return
     */
    private static String crmTypeToDbType(JSONObject fieldData) {
        String dbType;
        String crmType = fieldData.getString("itemType");
        if ("String".equals(crmType)) {
            Integer maxLength = fieldData.getInteger("maxLength");
            maxLength = (null == maxLength) ? 300 : maxLength;
            if (maxLength <= 1000) {
                //创建表时，如果VARCHAR大于8000，会报错
                dbType = "VARCHAR(" + maxLength + ")";
            } else {
                dbType = "text";
            }
        } else if ("Long".equals(crmType)) {
            dbType = "BIGINT";
        } else if ("Integer".equals(crmType)) {
            dbType = "INT";
        } else if ("Boolean".equals(crmType)) {
            dbType = "TINYINT";
        } else if ("Double".equals(crmType)) {
            //dbType = "DECIMAL";
            dbType = "Double";
        } else if ("Integer[]".equals(crmType)) {
            dbType = "VARCHAR(300)";
        } else if ("List<Map>".equals(crmType)) {
            dbType = "text";
        } else {
            dbType = "VARCHAR(300)";
        }
        return dbType;
    }

    /**
     *  根据 excel 表格 获取到crm 需要初始化的表
     * @return
     */
    public static List<String> getInitTableList() {
        List<String> initTableList = new ArrayList<>();
        try (
                InputStream inputStream = InitCrmBaseTable.class.getClassLoader().getResourceAsStream("CRM备份表清单.xlsx")
        ) {
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            rowIterator.next(); // 去除第一个行表头
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Cell cell = row.getCell(1);
                initTableList.add(cell.getStringCellValue());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  initTableList;
    }

}
